package org.vectrics.recruiter.job;

import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.vectrics.DataException;
import org.vectrics.SystemException;
import org.vectrics.common.db.Criteria;
import org.vectrics.company.Company;
import org.vectrics.domain.Domain;
import org.vectrics.hibernate.Filter;
import org.vectrics.hibernate.Selector;
import org.vectrics.hibernate.ThreadProperties;
import org.vectrics.location.Location;
import org.vectrics.provider.CoreServiceLocator;

public class JobSearcher {
	public static String BEAN_ID = "job.searcher";
	private transient Logger log = Logger.getLogger(this.getClass());
	
	/**
	 * find jobs with a query that match criteria
	 * @param criteria
	 * @param startIndex The first job index in the query to read.
	 * @param endIndex The last job index in the query to read.
	 * @return
	 */
	public List findJobs(JobSearchCriteria criteria, int startIndex, int endIndex) {
		log.debug("find Jobs, " +
				"\r\n - criteria = " + criteria +
				"\r\n - start Index = " + startIndex +
				"\r\n - end Index = " + endIndex
				);

		List jobList = null;
		Session session = ThreadProperties.getSession();

		Filter filter = getFilterForCriteria(criteria, true);
		filter.addSelectField("distinct(job)");

		if (log.isDebugEnabled())
			log.debug("findJobs() Statement:" + filter.getStatement());

		
		Query query = null;
		query = filter.createQuery(session);
		query.setCacheable(false);

		if ((endIndex >= 0) && (startIndex >= 0)) {
			query.setMaxResults(1 + endIndex - startIndex);
			query.setFirstResult(startIndex);
		}
		try {
			jobList = query.list();
		} catch (Exception ex) {
			log.error("Exception running query: \r\n"
					+ filter.getStatement());
			throw new SystemException("Exception running query: \r\n"
					+ filter.getStatement());
		}
		return(jobList);
	}

	/*
	 * (non-Javadoc)
	 * @see org.vectrics.recruiter.job.JobProvider#findJobCount(org.vectrics.recruiter.job.JobCriteria)
	 */
	public int findJobCount(JobSearchCriteria criteria) {
		Session session = ThreadProperties.getSession();
		int count = 0;

		Filter filter = getFilterForCriteria(criteria, false);
		filter.addSelectField("count(distinct job.id)");


		//if (log.isDebugEnabled())
			log.debug("findJobCount() Statement:" + filter.getStatement());
		Query query = filter.createQuery(session);
		query.setCacheable(false);
		Iterator results = query.iterate();

		if (results == null) {
			throw new DataException("Method does not define logic for criteria");
		} else {
			while ( results.hasNext() ) {
				Object row = (Object)results.next();
				Long countInteger = (Long)row;
				count = countInteger.intValue();
			}
		}
		return(count);
	}
	
	public Filter getFilterForCriteria(JobSearchCriteria criteria, boolean ordered) {
		log.debug("getFilterForCriteria() - criteria:\r\n  " + criteria.toString());
		Filter filter = new Filter();
		filter.setConjunction(Selector.CONJUNTION_AND);
		filter.addFrom("Job", "job");
		if (ordered) {
			filter.addOrderBy("job.datePosted desc");
		}
		
		if (criteria.getJobsNeedingApproval() == true) {
			if (criteria.getStatusTypes().size() > 0) {
				throw new SystemException("The criteria 'jobsNeedingApproval' was set to true, and status types were specified, status types should not be set.");
			}
			filter.addFrom("JobStatusTypeApproval", "approval");
			filter.addFrom("JobStatus", "jobStatus");
			filter.addCondition("jobStatus.job.id=job.id");
			filter.addCondition("jobStatus.dateEnd is null");
			filter.addCondition("approval.fromJobStatusType.code=jobStatus.statusType.code");
		}
		
		if (criteria.getCompanies() != null) {
			log.debug("Adding companies to query");
			Iterator iter = criteria.getCompanies().iterator();
			if (iter.hasNext()) {
				Selector companySelector = filter.getSelector()
					.addChildSelector("Company Selector", Selector.CONJUNTION_OR);
				while (iter.hasNext()) {
					Company company = (Company)iter.next();
					companySelector.addParameterCondition("job.company.id", 
							"=", company.getId());
					if (log.isDebugEnabled()) {
						log.debug("Looking for company = " + company.getName());
					}
				}
			}
		}
		
		if (criteria.getPerson() != null) {
			Selector personSelector = filter.getSelector().addChildSelector("Person selector", Selector.CONJUNTION_OR);
			personSelector.addParameterCondition("job.primaryRecruiter.id", "=", criteria.getPerson().getId());
			personSelector.addParameterCondition("job.secondaryRecruiter.id", "=", criteria.getPerson().getId());
			personSelector.addParameterCondition("job.hiringManager.id", "=", criteria.getPerson().getId());
		}

		if ((criteria.getDomains() != null) && (criteria.getDomains().size() > 0)) {
			Selector domainSelector = filter.getSelector().addChildSelector("Data owner selector", Selector.CONJUNTION_OR);
			Iterator iter = criteria.getDomains().iterator();
			while (iter.hasNext()) {
				Domain domain = (Domain)iter.next();
				if (domain.getId() == null)
					throw new SystemException("Data owner id is null");
				domainSelector.addParameterCondition("job.domain.id", "=", domain.getId());
			}
		} else {
			throw new SystemException("No domains specified for job criteria");
		}

		
		if (criteria.getReferenceCodes().size() > 0) {
			Selector refSelector = filter.getSelector().addChildSelector("Ref code selector", Selector.CONJUNTION_OR);
			Iterator iter = criteria.getReferenceCodes().iterator();
			while (iter.hasNext()) {
				String refCode = (String)iter.next();
				refSelector.addParameterCondition("job.referenceId", " like ", refCode);
			}
		}
		
		if (criteria.getStatusTypes().size() > 0) {
			filter.addFrom("JobStatus", "status");
			Selector statusSelector = filter.getSelector()
			      .addChildSelector("Status selector", Selector.CONJUNTION_OR);
			Iterator iter = criteria.getStatusTypes().iterator();
			while (iter.hasNext()) {
				String statusTypeCode = (String)iter.next();
				Selector selector = statusSelector.addChildSelector(Selector.CONJUNTION_AND);
				selector.addCondition("status.job.id = job.id");
				selector.addParameterCondition("status.statusType.code", "=", statusTypeCode);
				selector.addCondition("status.dateEnd is null");
				selector.addCondition("status.voided = 'F'");

				if (log.isDebugEnabled())
					log.debug("Sql will look for jobs with status = " + statusTypeCode);
			}
		}

		if (criteria.getSubStatusTypeCodes().size() > 0) {
			filter.addFrom("JobStatus", "status");
			Selector statusSelector = filter.getSelector()
			      .addChildSelector("Sub status selector", Selector.CONJUNTION_OR);
			Iterator iter = criteria.getSubStatusTypeCodes().iterator();
			while (iter.hasNext()) {
				String subStatusTypeCode = (String)iter.next();
				Selector selector = statusSelector.addChildSelector(Selector.CONJUNTION_AND);
				selector.addCondition("status.job.id = job.id");
				selector.addParameterCondition("status.statusType.subStatusType.code", "=", subStatusTypeCode);
				selector.addCondition("status.dateEnd is null");
				selector.addCondition("status.voided = 'F'");

				if (log.isDebugEnabled())
					log.debug("Sql will look for jobs with sub status = " + subStatusTypeCode);
			}
		}
		
		
		if (criteria.getVoidedType() == Criteria.VOID_YES) {
			filter.addCondition("job.voided='T'");
			log.debug("Looking for voided jobs");
		} else if (criteria.getVoidedType() == Criteria.VOID_NO) {
			filter.addCondition("job.voided='F'");
			log.debug("Looking for non-voided jobs");
		}

		if ((criteria.getJobFunctions() != null) && (criteria.getJobFunctions().size() > 0)) {
			log.debug("Adding companies to query");
			Iterator iter = criteria.getJobFunctions().iterator();
			if (iter.hasNext()) {
				Selector functionSelector = filter.getSelector()
					.addChildSelector("Function selector", Selector.CONJUNTION_OR);
				while (iter.hasNext()) {
					JobFunction jobFunction = (JobFunction)iter.next();
					if (jobFunction != null) {
						functionSelector.addParameterCondition("job.function.code", "=", jobFunction.getCode());

						if (log.isDebugEnabled())
							log.debug("Looking for job function: " + jobFunction.getCode());
					}
				}
			}
		}

		
		if ((criteria.getLocations() != null) && (criteria.getLocations().size() > 0)) {
			log.debug("Adding locations to query");
			Iterator iter = criteria.getLocations().iterator();
			if (iter.hasNext()) {
				Selector locationSelector = filter.getSelector()
					.addChildSelector("Location Selector", Selector.CONJUNTION_OR);
				while (iter.hasNext()) {
					Location location = (Location)iter.next();
					this.addJobLocationAndChildrenToSelector(location, locationSelector);
				}
			}
		}


		if (criteria.getSearchWords().size() > 0) {
			StringBuffer searchCondition = new StringBuffer();
			Iterator searchIter = criteria.getSearchWords().iterator();
			while (searchIter.hasNext()) {
				String word = (String)searchIter.next();
				searchCondition.append("(lower(job.title) like " + "'%" + word.toLowerCase() + "%'" + ")");
				searchCondition.append(" or ");

				searchCondition.append("(lower(job.description) like " + "'%" + word.toLowerCase() + "%'" + ")");
				searchCondition.append(" or ");

				searchCondition.append("(lower(job.searchWords) like " + "'%" + word.toLowerCase() + "%'" + ")");
				searchCondition.append(" or ");

				//searchCondition.append("(lower(job.title) like " + "'%" + word.toLowerCase() + "%'" + ")");
				//searchCondition.append(" or ");

				searchCondition.append("(lower(job.companyInfo) like " + "'%" + word.toLowerCase() + "%'" + ")");

				if (searchIter.hasNext())
					searchCondition.append(" or ");
			}

			filter.addCondition(searchCondition.toString());
		}

		if (criteria.getEarliestPostDate() != null) {
			filter.addParameterCondition("job.datePosted", ">=", criteria.getEarliestPostDate());
		}

		if (criteria.getLatestPostDate() != null) {
			filter.addParameterCondition("job.datePosted", "<=", criteria.getLatestPostDate());
		}

		if (criteria.getEarliestCreateDate() != null) {
			filter.addParameterCondition("job.createdDate", ">=", criteria.getEarliestCreateDate());
		}

		if (criteria.getLatestCreateDate() != null) {
			filter.addParameterCondition("job.createdDate", "<=", criteria.getLatestCreateDate());
		}

		if (criteria.getCandidateId() != null) {
			filter.addFrom("JobCandidate", "jobCandidate");
			filter.addCondition("jobCandidate.job.id = jobCandidate.job.id");
			filter.addParameterCondition("jobCandidate.candidate.id", "=", criteria.getCandidateId());
		}
		
		if (criteria.getTitle() != null) {
			filter.addCondition("job.title like '"+criteria.getTitle()+"%'");
		}
		
		if (criteria.getPrimaryRecruiter() != null) {
			filter.addCondition("job.primaryRecruiter.id = "+criteria.getPrimaryRecruiter().toString());
		}
		
		if (criteria.getRequester() != null) {
			filter.addCondition("job.requester.id = "+criteria.getRequester().toString());
		}
		
		return(filter);
	}

	private void addJobLocationAndChildrenToSelector(Location parent, Selector idSelector) {
		idSelector.addParameterCondition("job.location.id", "=", parent.getId());
		if (log.isDebugEnabled())
			log.debug("added implied criteria location: " + parent.getName());
		Collection children = CoreServiceLocator.getLocationService().findChildren(parent);
		Iterator iter = children.iterator();
		while (iter.hasNext()) {
			Location child = (Location)iter.next();
			this.addJobLocationAndChildrenToSelector(child, idSelector);
		}
	}
	
}
